const Router = require("@koa/router");
const Database = require("better-sqlite3");
const path = require('path');
require('dotenv').config({ path: path.resolve(__dirname, '../.env') });
/* app_doc_doc 数据结构：
{
  "id": 1,
  "name": "蒂梵尼灯：融合自然与艺术的设计典范",
  "cover_image": "cover/test.jpg",
  "content": "呵呵",
  "top_doc": 1,
  "create_time": "2025-09-07 14:41:35.246335",
  "modify_time": "2025-09-07 14:41:35.246354",
  "create_user_id": 2,
  "pre_content": "呵呵",
  "parent_doc": 0,
  "sort": 9999,
  "status": 1,
  "editor_mode": 3,
  "open_children": 0,
  "show_children": 0,
  "content_en": null,
  "name_en": null
}
*/

// 生成当前时间字符串，格式：YYYY-MM-DD HH:mm:ss.SSSSSS
function getCurrentTimeString() {
  const now = new Date();
  const year = now.getFullYear();
  const month = String(now.getMonth() + 1).padStart(2, '0');
  const day = String(now.getDate()).padStart(2, '0');
  const hours = String(now.getHours()).padStart(2, '0');
  const minutes = String(now.getMinutes()).padStart(2, '0');
  const seconds = String(now.getSeconds()).padStart(2, '0');
  const milliseconds = String(now.getMilliseconds()).padStart(3, '0');
  
  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}.${milliseconds}000`;
}

// SQLite数据库配置
const dbPath = process.env.DOC_PATH;//"/www/dk_project/dk_app/dk_mrdoc/config/db.sqlite3";
const db = new Database(dbPath, { verbose: console.log });

const router = new Router({ prefix: "/doc" });

// 获取文档列表接口（支持分页、状态筛选和关键词搜索）
router.get("/list", async (ctx) => {
  try {
    // 获取查询参数，设置默认值
    const page = parseInt(ctx.query.page) || 1;
    const limit = parseInt(ctx.query.limit) || 10;
    const status = ctx.query.status ? parseInt(ctx.query.status) : null;
    const keyword = ctx.query.keyword || null;
    const top_doc = ctx.query.top_doc ? parseInt(ctx.query.top_doc) : null;
    const offset = (page - 1) * limit;

    // 构建查询条件和参数
    let whereConditions = [];
    let queryParams = [];

    // 状态筛选条件
    if (status !== null) {
      whereConditions.push("status = ?");
      queryParams.push(status);
    }

    // top_doc筛选条件
    if (top_doc !== null) {
      whereConditions.push("top_doc = ?");
      queryParams.push(top_doc);
    }

    // 关键词搜索条件
    if (keyword) {
      whereConditions.push(`(name LIKE ? OR content LIKE ? OR name_en LIKE ? OR content_en LIKE ?)`);
      const likeKeyword = `%${keyword}%`;
      queryParams.push(likeKeyword, likeKeyword, likeKeyword, likeKeyword);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // 查询总记录数
    const countSql = `SELECT COUNT(*) as count FROM app_doc_doc ${whereClause}`;
    const countStmt = db.prepare(countSql);
    const totalCount = countStmt.get(...queryParams).count;

    // 查询分页数据
    const selectSql = `
      SELECT id, name, name_en, top_doc, create_time, content, status, cover_image FROM app_doc_doc
      ${whereClause}
      ORDER BY create_time DESC
      LIMIT ? OFFSET ?
    `;
    const selectStmt = db.prepare(selectSql);
    const rawList = selectStmt.all(...queryParams, limit, offset);
    
    // 处理列表数据，直接从cover_image字段读取封面图片
    const list = rawList.map(item => ({
      id: item.id,
      name: item.name,
      name_en: item.name_en,
      status: item.status,
      top_doc: item.top_doc,
      create_time: item.create_time,
      cover_image: item.cover_image
    }));

    // 计算总页数
    const totalPages = Math.ceil(totalCount / limit);

    ctx.body = {
      success: true,
      data: {
        list,
        pagination: {
          current: page,
          pageSize: limit,
          total: totalCount,
          totalPages: totalPages
        }
      }
    };
  } catch (error) {
    console.error("查询文档列表失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "查询文档列表失败",
      error: error.message
    };
  }
});

// 根据ID查询文档详情接口
router.get("/detail/:id", async (ctx) => {
  try {
    const id = parseInt(ctx.params.id);
    
    if (!id || isNaN(id)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "文档ID参数无效"
      };
      return;
    }

    // 查询文档详情
    const selectStmt = db.prepare(`
      SELECT * FROM app_doc_doc WHERE id = ?
    `);
    const doc = selectStmt.get(id);

    if (!doc) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "文档不存在"
      };
      return;
    }

    ctx.body = {
      success: true,
      data: doc
    };
  } catch (error) {
    console.error("查询文档详情失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "查询文档详情失败",
      error: error.message
    };
  }
});

// 新增文档接口
router.post("/create", async (ctx) => {
  try {
    const { name, content, top_doc, create_time, cover_image, content_en, name_en, status } = ctx.request.body;
    
    // 验证必填参数
    if (!name || !content || !top_doc) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "缺少必填参数：name, content, top_doc"
      };
      return;
    }
    
    // 如果create_time为空，使用当前时间
    const finalCreateTime = create_time || getCurrentTimeString();

    // 准备插入SQL语句
    const insertStmt = db.prepare(`
      INSERT INTO app_doc_doc (
        name, content, top_doc, create_time, create_user_id,
        modify_time, pre_content, parent_doc, sort, status,
        editor_mode, open_children, show_children, cover_image, content_en, name_en
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    // 执行插入操作
    const result = insertStmt.run(
      name,               // name
      content,            // content
      top_doc,            // top_doc
      finalCreateTime,    // create_time
      2,                  // create_user_id (写死为2)
      finalCreateTime,    // modify_time (初始值同create_time)
      "",                 // pre_content (写死为空字符串)
      0,                  // parent_doc (写死为0)
      9999,               // sort (写死为9999)
      status !== undefined ? parseInt(status) : 1, // status (默认为1)
      3,                  // editor_mode (写死为3)
      0,                  // open_children (写死为0)
      0,                  // show_children (写死为0)
      cover_image || null, // cover_image
      content_en || null,  // content_en
      name_en || null      // name_en
    );

    // 获取新插入的文档详情
    const selectStmt = db.prepare("SELECT * FROM app_doc_doc WHERE id = ?");
    const newDoc = selectStmt.get(result.lastInsertRowid);

    ctx.body = {
      success: true,
      message: "文档创建成功",
      data: newDoc
    };
  } catch (error) {
    console.error("创建文档失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "创建文档失败",
      error: error.message
    };
  }
});

// 编辑文档接口
router.put("/update/:id", async (ctx) => {
  try {
    const id = parseInt(ctx.params.id);
    const { name, content, top_doc, modify_time, cover_image, content_en, name_en, status } = ctx.request.body;
    
    // 验证ID参数
    if (!id || isNaN(id)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "文档ID参数无效"
      };
      return;
    }
    
    // 验证必填参数
    if (!name || !content || !top_doc) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "缺少必填参数：name, content, top_doc"
      };
      return;
    }
    
    // 如果modify_time为空，使用当前时间
    const finalModifyTime = modify_time || getCurrentTimeString();

    // 检查文档是否存在
    const checkStmt = db.prepare("SELECT id FROM app_doc_doc WHERE id = ?");
    const docExists = checkStmt.get(id);
    
    if (!docExists) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "文档不存在"
      };
      return;
    }

    // 准备更新SQL语句（允许修改更多字段）
    const updateStmt = db.prepare(`
      UPDATE app_doc_doc
      SET name = ?, content = ?, top_doc = ?, modify_time = ?,
          cover_image = ?, content_en = ?, name_en = ?, status = ?
      WHERE id = ?
    `);

    // 执行更新操作
    const result = updateStmt.run(
      name,               // name
      content,            // content
      top_doc,            // top_doc
      finalModifyTime,    // modify_time
      cover_image || null, // cover_image
      content_en || null,  // content_en
      name_en || null,     // name_en
      status !== undefined ? parseInt(status) : 1, // status (默认为1)
      id                  // id
    );

    if (result.changes === 0) {
      ctx.status = 500;
      ctx.body = {
        success: false,
        message: "文档更新失败"
      };
      return;
    }

    // 获取更新后的文档详情
    const selectStmt = db.prepare("SELECT * FROM app_doc_doc WHERE id = ?");
    const updatedDoc = selectStmt.get(id);

    ctx.body = {
      success: true,
      message: "文档更新成功",
      data: updatedDoc
    };
  } catch (error) {
    console.error("编辑文档失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "编辑文档失败",
      error: error.message
    };
  }
});

// 删除文档接口
router.delete("/delete/:id", async (ctx) => {
  try {
    const id = parseInt(ctx.params.id);
    
    // 验证ID参数
    if (!id || isNaN(id)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "文档ID参数无效"
      };
      return;
    }

    // 检查文档是否存在
    const checkStmt = db.prepare("SELECT id FROM app_doc_doc WHERE id = ?");
    const docExists = checkStmt.get(id);
    
    if (!docExists) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "文档不存在"
      };
      return;
    }

    // 准备删除SQL语句
    const deleteStmt = db.prepare("DELETE FROM app_doc_doc WHERE id = ?");
    
    // 执行删除操作
    const result = deleteStmt.run(id);

    if (result.changes === 0) {
      ctx.status = 500;
      ctx.body = {
        success: false,
        message: "文档删除失败"
      };
      return;
    }

    ctx.body = {
      success: true,
      message: "文档删除成功",
      data: {
        id: id,
        deleted: true
      }
    };
  } catch (error) {
    console.error("删除文档失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "删除文档失败",
      error: error.message
    };
  }
});

// 发布/取消发布文档接口
router.patch("/publish/:id", async (ctx) => {
  try {
    const id = parseInt(ctx.params.id);
    const { status } = ctx.request.body;
    
    // 验证ID参数
    if (!id || isNaN(id)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "文档ID参数无效"
      };
      return;
    }
    
    // 验证status参数
    if (status === undefined || (status !== 0 && status !== 1)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "status参数无效，必须是0（取消发布）或1（发布）"
      };
      return;
    }
    
    // 检查文档是否存在
    const checkStmt = db.prepare("SELECT id, name, status FROM app_doc_doc WHERE id = ?");
    const docExists = checkStmt.get(id);
    
    if (!docExists) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "文档不存在"
      };
      return;
    }
    
    // 准备更新SQL语句
    const updateStmt = db.prepare(`
      UPDATE app_doc_doc
      SET status = ?, modify_time = ?
      WHERE id = ?
    `);
    
    // 执行更新操作
    const result = updateStmt.run(
      status,
      getCurrentTimeString(), // modify_time
      id
    );
    
    if (result.changes === 0) {
      ctx.status = 500;
      ctx.body = {
        success: false,
        message: "文档状态更新失败"
      };
      return;
    }
    
    // 获取更新后的文档详情
    const selectStmt = db.prepare("SELECT id, name, status, modify_time FROM app_doc_doc WHERE id = ?");
    const updatedDoc = selectStmt.get(id);
    
    ctx.body = {
      success: true,
      message: status === 1 ? "文档发布成功" : "文档取消发布成功",
      data: updatedDoc
    };
  } catch (error) {
    console.error("更新文档发布状态失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "更新文档发布状态失败",
      error: error.message
    };
  }
});

// 更新文档封面图片接口
router.patch("/update-cover/:id", async (ctx) => {
  try {
    const id = parseInt(ctx.params.id);
    const { cover_image } = ctx.request.body;
    
    // 验证ID参数
    if (!id || isNaN(id)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "文档ID参数无效"
      };
      return;
    }
    
    // 验证cover_image参数
    if (cover_image === undefined) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "缺少必填参数：cover_image"
      };
      return;
    }
    
    // 检查文档是否存在
    const checkStmt = db.prepare("SELECT id, name, cover_image FROM app_doc_doc WHERE id = ?");
    const docExists = checkStmt.get(id);
    
    if (!docExists) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: "文档不存在"
      };
      return;
    }
    
    // 准备更新SQL语句（仅更新封面图片和修改时间）
    const updateStmt = db.prepare(`
      UPDATE app_doc_doc
      SET cover_image = ?, modify_time = ?
      WHERE id = ?
    `);
    
    // 执行更新操作
    const result = updateStmt.run(
      cover_image,            // cover_image
      getCurrentTimeString(), // modify_time
      id                      // id
    );
    
    if (result.changes === 0) {
      ctx.status = 500;
      ctx.body = {
        success: false,
        message: "封面图片更新失败"
      };
      return;
    }
    
    // 获取更新后的文档详情
    const selectStmt = db.prepare("SELECT id, name, cover_image, modify_time FROM app_doc_doc WHERE id = ?");
    const updatedDoc = selectStmt.get(id);
    
    ctx.body = {
      success: true,
      message: "封面图片更新成功",
      data: updatedDoc
    };
  } catch (error) {
    console.error("更新封面图片失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: "更新封面图片失败",
      error: error.message
    };
  }
});

// 更新文档创建时间接口
router.patch("/update-create-time/:id", async (ctx) => {
  try {
    const id = parseInt(ctx.params.id);
    
    // 验证ID参数
    if (!id || isNaN(id)) {
      ctx.status = 400;
      ctx.body = {
        success: false,
        message: "文档ID参数无效"
      };
      return;
    }
    
    // 检查文档是否存在
    const checkStmt = db.prepare("SELECT id, name FROM app_doc_doc WHERE id = ?");
    const docExists = checkStmt.get(id);
    
    if (!docExists) {
      ctx.status = 404;
      ctx.body = {
        success: false,
        message: '文档不存在'
      };
      return;
    }
    
    const currentTime = getCurrentTimeString();
    
    // 准备更新SQL语句并直接获取更新后的记录
    const updateStmt = db.prepare(`
      UPDATE app_doc_doc
      SET create_time = ?, modify_time = ?
      WHERE id = ?
    `);
    
    // 执行更新操作
    const result = updateStmt.run(
      currentTime,  // create_time
      currentTime,  // modify_time (同时更新修改时间)
      id            // id
    );
    
    if (result.changes === 0) {
      ctx.status = 500;
      ctx.body = {
        success: false,
        message: "文档创建时间更新失败"
      };
      return;
    }
    
    // 获取更新后的文档详情
    const selectStmt = db.prepare("SELECT * FROM app_doc_doc WHERE id = ?");
    const updatedDoc = selectStmt.get(id);
    
    ctx.body = {
      success: true,
      data: updatedDoc,
      message: '文档创建时间更新成功'
    };
  } catch (error) {
    console.error("更新文档创建时间失败:", error);
    ctx.status = 500;
    ctx.body = {
      success: false,
      message: '更新文档创建时间失败',
      error: error.message
    };
  }
});

// 导出路由
module.exports = router;